/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package apus.persistence.jdbc;
import apus.entity.AbstractEntity;
import apus.exception.DatabaseException;
import apus.persistence.AbstractDAO;
import java.sql.*;
import java.util.Properties;

/**
 * @author  Roman Dyatkovsky
 * @author  Maxim Vasilevsky
 * @since APUS v0.5
 */
public abstract class AbstractJdbcDAO <TypeEn extends AbstractEntity> implements AbstractDAO<TypeEn> {

    private final static String DRIVER_CLASS = "org.hsqldb.jdbcDriver";
    private final static String DB_URL = "jdbc:hsqldb:C:/Workspace/apusGoogle/database/ApusDB";
    private final static String DB_USER = "SA";
    private final static String DB_PASSWORD = "";
    private static Connection connection;
    
    public static Connection getConnection() {
        if (connection == null) {
            try {
                Properties properties = new Properties();
                properties.setProperty("user",DB_USER);
                properties.setProperty("password",DB_PASSWORD);
                properties.setProperty("useUnicode","true");
                properties.setProperty("encoding","UTF-8");
                properties.setProperty("characterEncoding","UTF-8");
                Class.forName(DRIVER_CLASS);
                connection = DriverManager.getConnection(DB_URL, properties);
            } catch (ClassNotFoundException | SQLException e) {
                throw new DatabaseException("Can't get connection", e);
            }
        }
        return connection;
    }
    
    public static int getNextId() {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select PVALUE from PARAMS where PKEY='ENTITY_ID'");
            int id = 0;
            if (rs.next()) {
                id = Integer.parseInt(rs.getString("PVALUE").trim());
            } else {
                st.execute("INSERT INTO PARAMS (PKEY,PVALUE) VALUES ('ENTITY_ID','0')");
            }
            id++;
            st.execute("update PARAMS set PVALUE=" + id + " where PKEY='ENTITY_ID'");
            return id;
        } catch (SQLException e) {
            throw new DatabaseException("Can't create new ID", e);
        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }
    
    
    public static int getNextUser() {
        Connection cn = getConnection();
        Statement st = null;
        ResultSet rs = null;
        try {
            st = cn.createStatement();
            rs = st.executeQuery("select PVALUE from PARAMS where PKEY='USER_ID'");
            int id = 0;
            if (rs.next()) {
                id = Integer.parseInt(rs.getString("PVALUE").trim());
            } else {
                st.execute("INSERT INTO PARAMS (PKEY,PVALUE) VALUES ('USER_ID','0')");
            }
            id++;
            st.execute("update PARAMS set PVALUE=" + id + " where PKEY='USER_ID'");
            return id;
        } catch (SQLException e) {
            throw new DatabaseException("Can't create new user", e);
        } finally {

            try {
                if (rs != null) {
                    rs.close();
                }
                if (st != null) {
                    st.close();
                }
            } catch (SQLException ex) {
                System.err.println("Can't close statement");
                ex.printStackTrace(System.err);
            }
        }
    }
        
}
